/**
 * 
 */
package com.itump.base.util;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.contrib.HSSFCellUtil;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
 * 
 * 标题: JAVA标准化管理子系统<br>
 * 作者： <br>
 * 功能描述: Excel操纵类，可以根据Excel模板来生成Excel对象<br>
 * 创建时间：2009-07-17 10:27:57<br>
 * 版本信息：1.0 <br>
 * Copyright: Copyright (c) 2009<br>
 * Company: <br>
 */
public class ExcelUtil {
	private static Log logger = LogFactory.getLog(ExcelUtil.class);

	private static final String DATAS = "datas";
//	private static final String DATAS2 = "datas1";

	private HSSFWorkbook workbook;

	private HSSFSheet sheet;
	
	private HSSFSheet sheet1;

	private HSSFRow currentRow;

	private Map<Integer, HSSFCellStyle> styles = new HashMap<Integer, HSSFCellStyle>(); // 数据行的默认样式配置

	private Map<String, HSSFCellStyle> confStyles = new HashMap<String, HSSFCellStyle>(); // 通过设置"#STYLE_XXX"来标识的样式配置

	private int initrow; // 数据输出起始行

	private int initcol; // 数据输出起始列

	private int num; // index number

	private int currentcol; // 当前列

	private int currentRowIndex; // 当前行index

	private int rowheight = 22; // 行高

	private int lastLowNum = 0;

	private String cellStyle = null;

	private ExcelUtil() {
	}

	/**
	 * 使用默认模板创建TMExcel对象
	 * 
	 * @return 根据模板已初始化完成的TMExcel对象
	 */
	public static ExcelUtil newInstance() {
		return newInstance("templates/default.xls");
	}

	/**
	 * 指定模板创建TMExcel对象
	 * 
	 * @param templates
	 *            模板名称
	 * @return 根据模板已初始化完成的TMExcel对象
	 */
	public static ExcelUtil newInstance(String templates) {
		try {
			ExcelUtil excel = new ExcelUtil();   
			POIFSFileSystem fs = new POIFSFileSystem(Thread.currentThread()
					.getContextClassLoader().getResourceAsStream(templates));
			
			//POIFSFileSystem fs = new POIFSFileSystem(new   FileInputStream(templates));
			excel.workbook = new HSSFWorkbook(fs);
			excel.sheet = excel.workbook.getSheetAt(0);
			// 查找配置
			excel.initConfig(DATAS);
			// 查找其它样式配置
			excel.readCellStyles();

			// 删除配置行
			excel.sheet.removeRow(excel.sheet.getRow(excel.initrow));

			return excel;
		} catch (Exception e) {
			e.printStackTrace();
			logger.trace("创建Excel对象出现异常", e);
			return null;
		}
	}
	/**
	 * 指定模板创建TMExcel对象
	 * 
	 * @param templates
	 *            模板名称
	 * @return 根据模板已初始化完成的TMExcel对象
	 */
	public static ExcelUtil newInstance(String templates,String flag) {
		try {
			ExcelUtil excel = new ExcelUtil();
			POIFSFileSystem fs = new POIFSFileSystem(Thread.currentThread()
					.getContextClassLoader().getResourceAsStream(templates));
			excel.workbook = new HSSFWorkbook(fs);
			excel.sheet = excel.workbook.getSheetAt(0);
			// 查找配置
			excel.initConfig(flag);

			// 查找其它样式配置
			excel.readCellStyles();

			// 删除配置行
			excel.sheet.removeRow(excel.sheet.getRow(excel.initrow));

			return excel;
		} catch (Exception e) {
			e.printStackTrace();
			logger.trace("创建Excel对象出现异常", e);
			return null;
		}
	}


	/**
	 * 初始化翻页的页数，以便于序列号的生成
	 * 
	 * @param num
	 *            初始编号
	 */
	public void initPageNumber(int num) {
		this.num = num;
	}

	/**
	 * 设置特定的单元格样式，此样式可以通过在模板文件中定义"#STYLE_XX"来得到，如： #STYLE_1，传入的参数就是"STYLE_1"
	 * 
	 * @param style
	 */
	public void setCellStyle(String style) {
		cellStyle = style;
	}

	/**
	 * 取消特定的单元格格式，恢复默认的配置值，即DATAS所在行的值
	 */
	public void setCellDefaultStyle() {
		cellStyle = null;
	}

	/**
	 * 创建新行
	 * 
	 * @param index
	 *            从0开始计数
	 */
	public void createRow(int index) {
		// 如果在当前插入数据的区域有后续行，则将其后面的行往后移动
		if (lastLowNum > initrow && index > 0) {
			sheet.shiftRows(index + initrow, lastLowNum + index, 1, true, true);
		}
		currentRow = sheet.createRow(index + initrow);
		currentRow.setHeight((short) rowheight);
		currentRowIndex = index;
		currentcol = initcol;
	}

	/**
	 * 根据传入的字符串值，在当前行上创建新列
	 * 
	 * @param value
	 *            列的值（字符串）
	 */
	public void createCell(String value) {
		HSSFCell cell = createCell();
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setCellValue(value);
	}
	
	/**
	 * 根据传入的字符串值，在当前行上创建新列
	 * 
	 * @param value
	 *            列的值（字符串）
	 */
	public HSSFCell createCellone(String value) {
		HSSFCell cell = createCell();
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setCellValue(value);
		return cell;
	}

	/**
	 * 根据传入的日期值，在当前行上创建新列 在这种情况下（传入日期），你可以在模板中定义对应列 的日期格式，这样可以灵活通过模板来控制输出的日期格式
	 * 
	 * @param value
	 *            日期
	 */
	public void createCell(Date value) {
		HSSFCell cell = createCell();
		cell.setCellValue(value);
	}
	
	/**
	 * 根据传入的字符串值，在当前行上创建新列
	 * 
	 * @param value
	 *            列的值（字符串）
	 */
	public void createCell(int value) {
		HSSFCell cell = createCell();
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
	}
	
	/**
	 * 根据传入的字符串值，在当前行上创建新列
	 * 
	 * @param value
	 *            列的值（字符串）
	 */
	public void createCell(double value) {
		HSSFCell cell = createCell();
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
	}

	/**
	 * 创建当前行的序列号列，通常在一行的开头便会创建 注意要使用这个方法，你必需在创建行之前调用initPageNumber方法
	 */
	public void createSerialNumCell() {
		HSSFCell cell = createCell();
		cell.setCellValue(currentRowIndex + num);
	}

	private HSSFCell createCell() {
		HSSFCell cell = currentRow.createCell((short) currentcol++);
		//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		HSSFCellStyle style = (HSSFCellStyle) styles.get(new Integer(cell
				.getCellNum()));
		if (style != null) {
			cell.setCellStyle(style);
		}

		// 设置了特定格式
		if (cellStyle != null) {
			HSSFCellStyle ts = (HSSFCellStyle) confStyles.get(cellStyle);
			if (ts != null) {
				cell.setCellStyle(ts);
			}
		}
		return cell;
	}

	/**
	 * 获取当前HSSFWorkbook的实例
	 * 
	 * @return
	 */
	public HSSFWorkbook getWorkbook() {
		return workbook;
	}

	/**
	 * 获取模板中定义的单元格样式，如果没有定义，则返回空
	 * 
	 * @param style
	 *            模板定义的样式名称
	 * @return 模板定义的单元格的样式，如果没有定义则返回空
	 */
	public HSSFCellStyle getTemplateStyle(String style) {
		return (HSSFCellStyle) confStyles.get(style);
	}

	/**
	 * 替换模板中的文本参数 参数以“#”开始
	 * 
	 * @param props
	 */
	public void replaceParameters(Properties props) {
		if (props == null || props.size() == 0) {
			return;
		}
		Set<?> propsets = props.entrySet();
		Iterator<?> rowit = sheet.rowIterator();
		while (rowit.hasNext()) {
			HSSFRow row = (HSSFRow) rowit.next();
			if (row == null)
				continue;
			int cellLength = row.getLastCellNum();
			for (int i = 0; i < cellLength; i++) {
				HSSFCell cell = (HSSFCell) row.getCell((short) i);
				if (cell == null)
					continue;
				String value = cell.getStringCellValue();
				if (value != null && value.indexOf("#") != -1) {
					for (Iterator<?> iter = propsets.iterator(); iter.hasNext();) {
						Map.Entry<?, ?> entry = (Map.Entry<?, ?>) iter.next();
						value = value.replaceAll("#" + entry.getKey(),
								(String) entry.getValue());
					}
				}
				//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(value);
			}
		}
		if(sheet1 != null){
			Iterator<?> rowit1 = sheet1.rowIterator();
			while (rowit1.hasNext()) {
				HSSFRow row = (HSSFRow) rowit1.next();
				if (row == null)
					continue;
				int cellLength = row.getLastCellNum();
				for (int i = 0; i < cellLength; i++) {
					HSSFCell cell = (HSSFCell) row.getCell((short) i);
					if (cell == null)
						continue;
					String value = cell.getStringCellValue();
					if (value != null && value.indexOf("#") != -1) {
						for (Iterator<?> iter = propsets.iterator(); iter.hasNext();) {
							Map.Entry<?, ?> entry = (Map.Entry<?, ?>) iter.next();
							value = value.replaceAll("#" + entry.getKey(),
									(String) entry.getValue());
						}
					}
					//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue(value);
				}
			}
		}
	}

	/**
	 * 合并单元格【与JSP页面的合并相同】，在同一列上合并相同值的行
	 * 
	 * @param beginCol
	 *            合并的起始列（包括本列）
	 * @param endCol
	 *            合并的结束列（包括本列）
	 */
	public void mergeTable(int beginCol, int endCol) {
		mergeT(sheet, beginCol, endCol, initrow, sheet.getLastRowNum() + 1
				- initrow);
	}

	private void mergeT(HSSFSheet currentSheet, int col, int maxCol,
			int minRows, int rowCount) {

		String theLastValue = null;
		int firstRow = minRows;
		int countNumber = 1;
		for (int i = minRows; i < minRows + rowCount; i++) {
			HSSFRow r = currentSheet.getRow(i);
			if (r == null)
				continue;
			HSSFCell cell = r.getCell((short) col);
			if ( null != cell )
			{
				String currentValue = Utils.chgNull(cell.getStringCellValue()); // the_table[i][col].innerText;
				if (theLastValue == null) {
					theLastValue = currentValue;
					continue;
				}
				if (theLastValue.equals(currentValue)) {
					// table_merge(t,the_table[i][col]);
					currentSheet.addMergedRegion(new Region(i - 1, (short) col, i,
							(short) col));
	
					countNumber = countNumber + 1;
					if (i + 1 >= minRows + rowCount && col < maxCol) {
						mergeT(currentSheet, col + 1, maxCol, firstRow, countNumber);
					}
					continue;
				}
				if (theLastValue != currentValue) {
					// the block end;
					if (col < maxCol) {
						mergeT(currentSheet, col + 1, maxCol, firstRow, countNumber);
					}
					theLastValue = currentValue;
	
					// next block
					firstRow = i;
					countNumber = 1;
				}
			}
		}
	}

	/**
	 * 初始化Excel配置
	 */
	@SuppressWarnings("unused")
	private void initConfig() {
		lastLowNum = sheet.getLastRowNum();
		Iterator<?> rowit = sheet.rowIterator();
		boolean configFinish = false;
		while (rowit.hasNext()) {
			if (configFinish) {
				break;
			}
			HSSFRow row = (HSSFRow) rowit.next();
			if (row == null)
				continue;
			int cellLength = row.getLastCellNum();
			// int rownum = row.getRowNum();
			for (int i = 0; i < cellLength; i++) {
				HSSFCell cell = (HSSFCell) row.getCell((short) i);
				if (cell == null)
					continue;
				String config = cell.getStringCellValue();
				if (DATAS.equalsIgnoreCase(config)) {
					// 本行是数据开始行和样式配置行，需要读取相应的配置信息
					initrow = row.getRowNum();
					rowheight = row.getHeight();
					initcol = cell.getCellNum();
					configFinish = true;
				}
				if (configFinish) {
					readCellStyle(cell);
				}
			}
		}
	}
	/**
	 * 初始化Excel配置
	 */
	private void initConfig(String flag) {
		lastLowNum = sheet.getLastRowNum();
		Iterator<?> rowit = sheet.rowIterator();
		boolean configFinish = false;
		while (rowit.hasNext()) {
			if (configFinish) {
				break;
			}
			HSSFRow row = (HSSFRow) rowit.next();
			if (row == null)
				continue;
			int cellLength = row.getLastCellNum();
			// int rownum = row.getRowNum();
			for (int i = 0; i < cellLength; i++) {
				HSSFCell cell = (HSSFCell) row.getCell((short) i);
				if (cell == null)
					continue;
				String config = cell.getStringCellValue();
				if (flag.equalsIgnoreCase(config)) {
					// 本行是数据开始行和样式配置行，需要读取相应的配置信息
					initrow = row.getRowNum();
					rowheight = row.getHeight();
					initcol = cell.getCellNum();
					configFinish = true;
				}
				if (configFinish) {
					readCellStyle(cell);
				}
			}
		}
	}
	/**
	 * 读取cell的样式
	 * 
	 * @param cell
	 */
	private void readCellStyle(HSSFCell cell) {
		HSSFCellStyle style = cell.getCellStyle();
		if (style == null)
			return;
		styles.put(new Integer(cell.getCellNum()), style);
	}

	/**
	 * 读取模板中其它单元格的样式配置
	 */
	private void readCellStyles() {
		Iterator<?> rowit = sheet.rowIterator();
		while (rowit.hasNext()) {
			HSSFRow row = (HSSFRow) rowit.next();
			if (row == null)
				continue;
			int cellLength = row.getLastCellNum();
			for (int i = 0; i < cellLength; i++) {
				HSSFCell cell = (HSSFCell) row.getCell((short) i);
				if (cell == null)
					continue;
				String value = cell.getStringCellValue();
				if (value != null && value.indexOf("#STYLE_") != -1) {
					HSSFCellStyle style = cell.getCellStyle();
					if (style == null)
						continue;
					confStyles.put(value.substring(1), style);

					// remove it
					row.removeCell(cell);
				}
			}
		}
	}

	/**
	 * 功能: 指定合并区域
	 * 
	 * @param rowForm
	 *            起始行
	 * @param colFrom
	 *            起始列
	 * @param rowTo
	 *            截止行
	 * @param colTo
	 *            截止列
	 */
	public void mergedRegion(int rowForm, int colFrom, int rowTo, int colTo, String content) {
		Region region = new Region(rowForm, (short) colFrom, rowTo,
				(short) colTo);
		if (!Utils.chgNull(cellStyle).equals("")) {
			setRegionStyle(region, cellStyle);
		}
		HSSFCell cell = sheet.getRow(rowForm).getCell((short)colFrom);
		//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(content); 
		sheet.addMergedRegion(region);
		currentcol = colTo+1;
	}
	
	/**
	 * 功能: 指定合并区域
	 * 
	 * @param rowForm
	 *            起始行
	 * @param colFrom
	 *            起始列
	 * @param rowTo
	 *            截止行
	 * @param colTo
	 *            截止列
	 */
	public void mergedRegion(int rowForm, int colFrom, int rowTo, int colTo, int content) {
		Region region = new Region(rowForm, (short) colFrom, rowTo,
				(short) colTo);
		if (!Utils.chgNull(cellStyle).equals("")) {
			setRegionStyle(region, cellStyle);
		}
		HSSFCell cell = sheet.getRow(rowForm).getCell((short)colFrom);
		//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(content);
		sheet.addMergedRegion(region);
		currentcol = colTo+1;
	}

	/**
	 * 功能: 设置区域样式
	 * 
	 * @param region
	 * @param style
	 */
	private void setRegionStyle(Region region, String style) {
		HSSFCellStyle cellStyle = getTemplateStyle(style);
		if (cellStyle != null) {
			for (int i = region.getRowFrom(); i <= region.getRowTo(); i++) {
				HSSFRow row = HSSFCellUtil.getRow(i, sheet);
				for (int j = region.getColumnFrom(); j <= region.getColumnTo(); j++) {
					HSSFCell cell = HSSFCellUtil.getCell(row, (short) j);
					cell.setCellStyle(cellStyle);
				}
			}
		}
	}

	// Test ExcelUtil
	public static void main(String[] args) {
		try {
			ExcelUtil excel = new ExcelUtil();
			POIFSFileSystem fs = new POIFSFileSystem(Thread.currentThread()
					.getContextClassLoader().getResourceAsStream("temp.xls"));
			excel.workbook = new HSSFWorkbook(fs);
			excel.sheet = excel.workbook.getSheetAt(0);

			excel.mergeTable(3, 3);

			FileOutputStream fileOut = new FileOutputStream("E:/temp.xls");
			excel.getWorkbook().write(fileOut);
			fileOut.close();
			/**
			 ExcelUtil excel = ExcelUtil.newInstance("templates/emergencystaffroles.xls"); 
			try 
	        {
				excel.initPageNumber(0);
				for(int i =0;i<list.size();i++)
				{
					excel.createRow(i);
					Rolestaff st = (Rolestaff)list.get(i);
					String role = st.getRole().getRolename();			
					String staffName = st.getStaff().getStaffName();
					String staffNo = st.getStaff().getStaffNo();
					String deptName = st.getDept().getDeptName();
					excel.createCell(i+1);  
					excel.createCell(staffNo);  
					excel.createCell(staffName);  
					excel.createCell(deptName); 

				}
				//writeExcel(excel,response);
				response.reset();
				response.setContentType("application/x-msdownload;charset=GBK");
				response.setHeader("Content-Disposition", "attachment;filename=temp.xls");
				OutputStream os=response.getOutputStream();
				excel.getWorkbook().write(os);
			 */
		} catch (IOException e) {
			e.printStackTrace();

		}
	}

	/**
	 * 功能: 返回当前行
	 * @return
	 */
	public int getCurrentRowIndex() {
		return currentRowIndex;
	}
	
	/**
	 * 指定模板创建TMExcel对象
	 * 
	 * @param templates
	 *            模板名称
	 * @return 根据模板已初始化完成的TMExcel对象
	 */
	public static ExcelUtil newInstance1(String templates) {
		try {
			ExcelUtil excel = new ExcelUtil();
			POIFSFileSystem fs = new POIFSFileSystem(Thread.currentThread()
					.getContextClassLoader().getResourceAsStream(templates));
			excel.workbook = new HSSFWorkbook(fs);
			excel.sheet = excel.workbook.getSheetAt(0);
			excel.sheet1 = excel.workbook.getSheetAt(1);
			// 查找配置
			excel.initConfig(DATAS);
			// 查找其它样式配置
			excel.readCellStyles();

			// 删除配置行
			excel.sheet.removeRow(excel.sheet.getRow(excel.initrow));
			return excel;
		} catch (Exception e) {
			e.printStackTrace();
			logger.trace("创建Excel对象出现异常", e);
			return null;
		}
	}
}